package dao.impl.StoreDaoImpl;

import common.model.Entity.StoreEntity.OrderItem;
import dao.StoreDao.OrderItemDao;
import common.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;

/**
 * 订单项数据访问接口实现类，提供订单项相关的数据库操作实现
 */
public class OrderItemDaoImpl implements OrderItemDao {
    private static final Logger logger = LoggerFactory.getLogger(OrderItemDaoImpl.class);
    private final QueryRunner queryRunner = new QueryRunner();

    @Override
    public Optional<OrderItem> findById(Long id) {
        String sql = "SELECT id, order_id AS orderId, product_id AS productId, quantity, " +
                "unit_price AS price, is_deleted AS isDeleted, " +
                "created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_order_item WHERE id = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            OrderItem orderItem = queryRunner.query(conn, sql, new BeanHandler<>(OrderItem.class), id);
            return Optional.ofNullable(orderItem);
        } catch (SQLException e) {
            logger.error("根据ID查询订单项失败: id={}", id, e);
            return Optional.empty();
        }
    }

    @Override
    public List<OrderItem> findByOrder(long orderId) {
        String sql = "SELECT id, order_id AS orderId, product_id AS productId, quantity, " +
                "unit_price AS price, is_deleted AS isDeleted, " +
                "created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_order_item WHERE order_id = ? AND is_deleted = 0 ORDER BY created_at DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(OrderItem.class), orderId);
        } catch (SQLException e) {
            logger.error("根据订单ID查询订单项失败: orderId={}", orderId, e);
            return List.of();
        }
    }

    @Override
    public List<OrderItem> findAll() {
        String sql = "SELECT id, order_id AS orderId, product_id AS productId, quantity, " +
                "unit_price AS price, is_deleted AS isDeleted, " +
                "created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_order_item WHERE is_deleted = 0 ORDER BY created_at DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(OrderItem.class));
        } catch (SQLException e) {
            logger.error("查询所有订单项失败", e);
            return List.of();
        }
    }

    @Override
    public boolean deleteById(Long id) {
        String sql = "UPDATE t_order_item SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql, id);
            return affected > 0;
        } catch (SQLException e) {
            logger.error("删除订单项失败: id={}", id, e);
            return false;
        }
    }

    @Override
    public boolean deleteByOrder(Long orderId) {
        String sql = "UPDATE t_order_item SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP WHERE order_id = ?";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql, orderId);
            return affected > 0;
        } catch (SQLException e) {
            logger.error("根据订单ID删除订单项失败: orderId={}", orderId, e);
            return false;
        }
    }

    @Override
    public boolean save(OrderItem orderItem) {
        String sql = "INSERT INTO t_order_item (order_id, product_id, quantity, unit_price, total_price) VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection()) {
            // 计算总价 = 单价 × 数量
            BigDecimal unitPrice = BigDecimal.valueOf(orderItem.getPrice());
            BigDecimal quantity = BigDecimal.valueOf(orderItem.getQuantity());
            BigDecimal totalPrice = unitPrice.multiply(quantity);

            int affected = queryRunner.update(conn, sql,
                    orderItem.getOrderId(),
                    orderItem.getProductId(),
                    orderItem.getQuantity(),
                    orderItem.getPrice(),
                    totalPrice);
            return affected > 0;
        } catch (SQLException e) {
            logger.error("保存订单项失败: orderId={}, productId={}",
                    orderItem.getOrderId(), orderItem.getProductId(), e);
            return false;
        }
    }

    @Override
    public boolean update(OrderItem orderItem) {
        String sql = "UPDATE t_order_item SET order_id = ?, product_id = ?, quantity = ?, unit_price = ?, total_price = ?, " +
                "updated_at = CURRENT_TIMESTAMP WHERE id = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            // 计算总价 = 单价 × 数量
            BigDecimal unitPrice = BigDecimal.valueOf(orderItem.getPrice());
            BigDecimal quantity = BigDecimal.valueOf(orderItem.getQuantity());
            BigDecimal totalPrice = unitPrice.multiply(quantity);

            int affected = queryRunner.update(conn, sql,
                    orderItem.getOrderId(),
                    orderItem.getProductId(),
                    orderItem.getQuantity(),
                    orderItem.getPrice(),
                    totalPrice,
                    orderItem.getId());
            return affected > 0;
        } catch (SQLException e) {
            logger.error("更新订单项失败: id={}", orderItem.getId(), e);
            return false;
        }
    }

    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM t_order_item WHERE is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new ScalarHandler<>());
        } catch (SQLException e) {
            logger.error("统计订单项数量失败", e);
            return 0;
        }
    }
}